package servlets;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class LabTestDAO {
	
	private static final String CREATE_LAB_TEST = "INSERT INTO qdlc_lab_test (name, price, categoryId) VALUES (?,?,?)";
	private static final String UPDATE_LAB_TEST = "UPDATE qdlc_lab_test SET name = ?, price = ?, categoryId = ? WHERE labTestId = ?";
	private static final String GET_LAB_TEST_FROM_ID = "SELECT * FROM qdlc_lab_test WHERE labTestId = ?;";
	private static final String GET_ALL_LAB_TEST = "SELECT * FROM qdlc_lab_test;";
	
	private SimpleDateFormat formatter = new SimpleDateFormat(
            "yyyy-MM-dd HH:mm:ss");
    
    
    public LabTest createLabTest(String name, float price, int categoryId) {
        LabTest labTest = null;

        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(CREATE_LAB_TEST,
                    PreparedStatement.RETURN_GENERATED_KEYS);

            st.setString(1, name);
            st.setFloat(2, price);
            st.setInt(3, categoryId);

            int affectedRows = st.executeUpdate();

            // Don't attempt to get keys if there is no affected row
            if (affectedRows > 0) {
                ResultSet keys = st.getGeneratedKeys();

                // Get key, return newUser
                if (keys.next()) {
                    int labTestId = keys.getInt(1);

                    labTest.setLabTestId(labTestId);
                    labTest.setName(name);
                    labTest.setPrice(price);
                    labTest.setCategoryId(categoryId);
                    
                    if (keys != null) {
                        keys.close();
                    }
                }
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return labTest;
    }

    public void updateLabTest(String name, float price, int categoryId, int labTestId) {
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        try {
            PreparedStatement st = conn.prepareStatement(UPDATE_LAB_TEST);
            st.setString(1, name);
            st.setFloat(2, price);
            st.setInt(4, categoryId);
            st.setInt(5, labTestId);
            
            
            st.executeUpdate();

            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }
    }

    public LabTest getLabTest(int labTestId) {
       LabTest labTest = null;
    
        // TODO: Add exceptions
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(GET_LAB_TEST_FROM_ID);

            st.setInt(1, labTestId);

            ResultSet rs = st.executeQuery();

            // Found result!
            if (rs.next()) {
                labTest = new LabTest();
                labTest.setLabTestId(rs.getInt("labTestId"));
                labTest.setName(rs.getString("name"));
                labTest.setPrice(rs.getFloat("price"));
                labTest.setCategoryId(rs.getInt("categoryId"));
            }

            if (rs != null) {
                rs.close();
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return labTest;
    }
    
    public ArrayList<LabTest> getAllLabTest() {
        ArrayList<LabTest> labTestList = new ArrayList<LabTest>();
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(GET_ALL_LAB_TEST);

            ResultSet rs = st.executeQuery();

            LabTestCategoryDAO dao = new LabTestCategoryDAO();
            // Found result!
            while (rs.next()) {
            	LabTest labTest = new LabTest();
                labTest.setLabTestId(rs.getInt("labTestId"));
                labTest.setName(rs.getString("name"));
                labTest.setPrice(rs.getFloat("price"));
                labTest.setCategoryId(rs.getInt("categoryId"));
                labTest.setLabTestCategory(dao.getLabTestCategory(rs.getInt("categoryId")));
                
                labTestList.add(labTest);
            }

            if (rs != null) {
                rs.close();
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return labTestList;
    }
		    

}
